package sv.gob.mh.dgii.dao.corr;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import org.apache.commons.beanutils.DynaBean;
import org.springframework.stereotype.Repository;
import org.springframework.transaction.annotation.Transactional;
import sv.gob.mh.dgii.dao.hibernate.HibernateDaoNoCrudImpl;
import sv.gob.mh.dgii.common.Utils;
import sv.gob.mh.dgii.xml.XmlResultSet;

@Repository
public class CorrConsultaDaoImpl extends HibernateDaoNoCrudImpl implements CorrConsultaDao{

	@Transactional
	public List<DynaBean> getXmlConsulta(String nit, 
										 String tipoSolicitud, 
										 String numeroResolucion,
										 String folioSolicitud)
	throws SQLException {
	List<Object> params = new ArrayList<Object>();
	String sql = "SELECT   dr.nit AS \"nit\", "
			+ "         siit.pkg_ruc_utils.get_nombre (rr.s_nombres, "
			+ "                                        rr.s_1ape_rasoc, "
			+ "                                        rr.s_2ape_abrev) "
			+ "            AS \"nombre\", " 
			+ "         dr.c_declaracion AS \"cdeclaracion\", "
			+ "         frd.s_siglas AS \"tiposolicitud\", "						
			+ "			dr.n_folio AS \"numSolicitud\", "
			+ "         drd.n_resolucion AS \"numresolucion\", "
			+ "         TO_CHAR(dr.f_presentacion,'dd/MM/yyyy') AS \"fingreso\", "
			+ "         dr.c_usuario AS \"usuario\", "
			+ "         dr.n_nota_abono AS \"notaabono\", "
			+ "         drd.m_estado AS \"estado\", "
			+ "         drd.nit_imprenta AS \"nitImprenta\", "
			+ "         drd.c_tipo_resolucion AS \"tiporesol\", "
			+ "         rr.c_clase AS \"tipocontrib\" "
			+ "  FROM   EDADEPUDOC.ED_DECLARACIONES dr, "
			+ "        EDADEPUDOC.ED_SOLICITUD_CORR drd, RUC.RC_RUC rr, "
			+ "        CATALOGOS.tb_formularios frd "
			+ " WHERE       dr.c_declaracion = drd.i_indice "
			+ "         AND rr.nit = dr.nit "
			+ "         AND frd.c_formulario = dr.c_formulario ";
	
		if (!Utils.isNullOrEmpty(nit)) {
			sql += "   AND dr.nit = ?  ";
			params.add(nit);
		}
		if (!Utils.isNullOrEmpty(tipoSolicitud)) {
			sql += "   AND dr.c_formulario = ?  ";
			params.add(tipoSolicitud);
		}
		if (!Utils.isNullOrEmpty(numeroResolucion)) {
			sql += "   AND drd.n_resolucion = ?  ";
			params.add(numeroResolucion);
		}
		if (!Utils.isNullOrEmpty(folioSolicitud)) {
			sql += "   AND (dr.n_folio) = ?  ";
			params.add(folioSolicitud);
		}
	
		sql += " order by dr.f_presentacion desc ";
		Object[] parameters = new Object[params.size()];
		int i = 0;
		for (Object element : params) {
			parameters[i] = element;
			i++;
		}
		return getJdbcHelper().getListDynaBean(sql, parameters);
	}
		
	public String getXmlArchivosResolucion(String numResolucion) throws SQLException {
		String sql = " select "
				+ "    n_resolucion \"numResolucion\", "
				+ "    n_resolucion || '-' ||n_version || '.' || substr(s_nom_archivo , -3) \"nombreArchivo\", "
				+ "    pkg_siit_functions.getListadoDetUniqueValor('DC','P_RESOLUCIONES', 2 )  \"ruta\" , "
				+ "    substr(s_nom_archivo , -3)  \"extension\" " + "from  "
				+ "dc_control_archivo " + "where n_resolucion  = ? ";
		
		Object[] parameters = new Object[1];
		
		parameters[0] = numResolucion;
		
		sql = "select * from (" + sql + ") A where rownum < " + 200;
		
		XmlResultSet rs = getJdbcHelper().getXmlResultSet(sql, parameters);
		return rs.getXml();
	
	}
	
	@SuppressWarnings("unchecked")
	public List<DynaBean> getArchivos(String nresolucion) {
		String name = "DcResolucion.findResolucion";
		Map<String, Object> params = new HashMap<String, Object>();
		params.put("p_n_resolucion", nresolucion);
		return executeSqlNamedQuery(name, params);
	}
	
	public List<DynaBean> getXmlConsultaDocs(String indice) throws SQLException {
		List<Object> params = new ArrayList<Object>();
		
		String sql = "SELECT   a.n_correlativo AS \"correlativo\", "			
				+ "         b.d_documento AS \"tipodoc\", "
				+ "         a.n_documento_sol AS \"cantidad\", "						
				+ "			a.n_serie_doc AS \"serie\", "
				+ "         a.n_autoriza_inicial AS \"iniAuto\", "
				+ "         a.m_estado AS \"estado\", "
				+ "         a.n_autoriza_final AS \"finAuto\" "			
				+ "  FROM   EDADEPUDOC.ED_SOL_DOCIM_FUNIC a, "
				+ "        IMPRENTAS.IM_TIPO_DOCUM b"
				+ " WHERE       b.c_tipo_documento=a.c_tipo_documento ";
		
			if (!Utils.isNullOrEmpty(indice)) {
				sql += "   AND a.i_indice = ? ";			
				params.add(indice);
			}
			
		
			Object[] parameters = new Object[params.size()];
			int i = 0;
			for (Object element : params) {
				parameters[i] = element;
				i++;
			}		
		return getJdbcHelper().getListDynaBean(sql, parameters);
	}
	
	
	
	
	public List<DynaBean> getXmlConsultaDocs941(String indice) throws SQLException {
		List<Object> params = new ArrayList<Object>();
		
		String sql = "SELECT   a.n_caja AS \"ncaja\", "			
				+ "         'TIQUETE' AS \"tipodoc\", "
				+ "         a.n_tiquete_sol AS \"cantidad\", "						
				+ "			a.n_serie_doc AS \"serie\", "
				+ "         a.n_autoriza_inicial AS \"iniAuto\", "
				+ "         a.m_estado AS \"estado\", "			
				+ "         a.n_autoriza_caja AS \"nautoriza\", "
				+ "         TO_CHAR(a.f_autoriza_caja,'dd/MM/yyyy') AS \"fautoriza\", "
				+ "         a.n_serie_caja AS \"nseriecaja\", "			
				+ "         a.n_autoriza_final AS \"finAuto\" "			
				+ "  FROM   EDADEPUDOC.ED_SOL_DOC_TIQUETE a "			
				+ " WHERE  ";
		
			if (!Utils.isNullOrEmpty(indice)) {
				sql += "   a.i_indice = ? ";			
				params.add(indice);
			}
			
		
			Object[] parameters = new Object[params.size()];
			int i = 0;
			for (Object element : params) {
				parameters[i] = element;
				i++;
			}
			return getJdbcHelper().getListDynaBean(sql, parameters);
	}
	
	
	
}
